﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess_Layer
{
    public static class Product_DAC
    {
        public static List<ProductWithCatNameDTO> getAllWithCatName()
        {
            List<ProductWithCatNameDTO> products = null;

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Get_All_Product_With_CategoryName");

                if (rdr.HasRows)
                {
                    products = new List<ProductWithCatNameDTO>();
                    while (rdr.Read())
                    {
                        ProductWithCatNameDTO prod = createProductWithCatName(ref rdr);
                        products.Add(prod);
                    }
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return products;
        }
        public static List<ProductWithCatNameDTO> getAllWithCatNameUpdate()
        {
            List<ProductWithCatNameDTO> products = null;

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Get_All_Product_With_CategoryNameUpdate");

                if (rdr.HasRows)
                {
                    products = new List<ProductWithCatNameDTO>();
                    while (rdr.Read())
                    {
                        ProductWithCatNameDTO prod = createProductWithCatName(ref rdr);
                        products.Add(prod);
                    }
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return products;
        }


        private static ProductWithCatNameDTO createProductWithCatName(ref SqlDataReader rdr)
        {
            ProductWithCatNameDTO prod = new ProductWithCatNameDTO();
           
            prod.ID = (long)rdr["ID"];
            prod.Price = (long)rdr["Price"];
            prod.Total = (long)rdr["Total"];
            prod.Score = (long)rdr["Score"];

            prod.Name = (string)rdr["Name"];
            prod.CatName = (string)rdr["CatName"];
            prod.TenTacGia = (string)rdr["TenTacGia"];
            prod.Image = (string)rdr["Image"];
            prod.CatID = (int)rdr["CatID"];
            //prod.ImportDate = (DateTime)rdr["ImportDate"];
            prod.SellDate = (DateTime)rdr["SellDate"];
            prod.SellExpiredDate = (DateTime)rdr["SellExpiredDate"];
            //prod.DeliveryDate = (DateTime)rdr["DeliveryDate"];
            //prod.DeliveryExpiredDate = (DateTime)rdr["DelliveryExpiredDate"];
            prod.Tennxb = (string)rdr["tennxb"];
            prod.Chieudai =rdr["chieudai"].ToString();
            prod.Chieurong = rdr["chieurong"].ToString();
            prod.Khoiluong = rdr["khoiluong"].ToString();
            prod.Rank = rdr["rank"].ToString();
            prod.Giamgia = rdr["giamgia"].ToString();
            return prod;
        }

        public static long insert(ProductDTO newProd)
        {
            List<SqlParameter> sqlParams = makeInsertParams(newProd);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int newProdID;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Insert_Product", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                newProdID = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return newProdID;
        }
        private static List<SqlParameter> makeInsertParams(ProductDTO newProd)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@name", SqlDbType.NVarChar, 50);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.Name;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@image", SqlDbType.NVarChar, 50);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.Image;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@catID", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.CatID;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@total", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.Total;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@price", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.Price;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@score", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.Score;
            sqlParams.Add(inParam);
            
            inParam = new SqlParameter("@sellDate", SqlDbType.DateTime);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.SellDate;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@sellExpiredDate", SqlDbType.DateTime);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newProd.SellExpiredDate;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static bool update(ProductDTO updatedProd)
        {
            List<SqlParameter> sqlParams = makeUpdateParams(updatedProd);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int count;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Update_Product", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                count = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return (count == 1);
        }
        private static List<SqlParameter> makeUpdateParams(ProductDTO updatedProd)
        {
            List<SqlParameter> sqlParams = makeInsertParams(updatedProd);

            SqlParameter inParam = new SqlParameter("@id", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = updatedProd.ID;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static string getEbookDownloadLink(long productID)
        {
            return DataAccessHelper.DB.Products.Where(pp => pp.ID == productID).Select(pp => pp.DownloadLink).First();
        }

        public static object getAll_()
        {
            return DataAccessHelper.DB.Products.Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
        }

        public static object search(string tenSP, double? giaTu, double? giaDen)
        {
            if (tenSP != null && giaTu != null && giaDen != null) {
                return DataAccessHelper.DB.Products.Where(pp => pp.Name.Contains(tenSP) && pp.RetailPrice >= giaTu && pp.RetailPrice <= giaDen).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            if (tenSP == null && giaTu != null && giaDen != null)
            {
                return DataAccessHelper.DB.Products.Where(pp => pp.RetailPrice >= giaTu && pp.RetailPrice <= giaDen).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            if (tenSP != null && giaTu == null && giaDen != null)
            {
                return DataAccessHelper.DB.Products.Where(pp => pp.Name.Contains(tenSP) && pp.RetailPrice <= giaDen).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            if (tenSP != null && giaTu != null && giaDen == null)
            {
                return DataAccessHelper.DB.Products.Where(pp => pp.Name.Contains(tenSP) && pp.RetailPrice >= giaTu).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            if (tenSP == null && giaTu == null && giaDen != null)
            {
                return DataAccessHelper.DB.Products.Where(pp => pp.RetailPrice <= giaDen).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            if (tenSP == null && giaTu != null && giaDen == null)
            {
                return DataAccessHelper.DB.Products.Where(pp => pp.RetailPrice >= giaTu).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            if (tenSP != null && giaTu == null && giaDen == null)
            {
                return DataAccessHelper.DB.Products.Where(pp => pp.Name.Contains(tenSP)).Select(pp => new { TenLSP= pp.Category.Name, TenNXB = pp.NhaXuatBan.Ten, TenTG = pp.TacGia.Ten, pp.Image, pp.ID, pp.Name, pp.Decription, pp.RetailPrice, pp.TotalQuantity, pp.MinQuant, pp.GiamGia, pp.ChieuDai, pp.ChieuRong, pp.KhoiLuong, pp.Ranking, pp.DownloadLink, pp.DaXoa, pp.ImportDate, pp.MaTacGia, pp.MaNhaXB, pp.CategoryID });
            }

            return getAll_();
        }

        public static void delete(List<long> deletedIDs)
        {
            foreach (long deletedID in deletedIDs)
            {
                Product nccgh = DataAccessHelper.DB.Products.Where(ncc => ncc.ID == deletedID).Select(ncc => ncc).First();
                DataAccessHelper.DB.Products.DeleteOnSubmit(nccgh);
            }

            DataAccessHelper.Save();
        }

        public static void update(Product nCC_GiangHang)
        {
            Product nccgh = DataAccessHelper.DB.Products.Where(ncc => ncc.ID == nCC_GiangHang.ID).Select(ncc => ncc).First();
            nccgh.CategoryID = nCC_GiangHang.CategoryID;
            nccgh.ChieuDai = nCC_GiangHang.ChieuDai;
            nccgh.ChieuRong = nCC_GiangHang.ChieuRong;
            nccgh.Decription = nCC_GiangHang.Decription;
            nccgh.DownloadLink = nCC_GiangHang.DownloadLink;
            nccgh.Image = nCC_GiangHang.Image;
            nccgh.ImportDate = nCC_GiangHang.ImportDate;
            nccgh.KhoiLuong = nCC_GiangHang.KhoiLuong;
            nccgh.MaNhaXB = nCC_GiangHang.MaNhaXB;
            nccgh.MaTacGia = nCC_GiangHang.MaTacGia;
            nccgh.Name = nCC_GiangHang.Name;
            nccgh.RetailPrice = nCC_GiangHang.RetailPrice;
            nccgh.TotalQuantity = nCC_GiangHang.TotalQuantity;
            nccgh.DaXoa = nCC_GiangHang.DaXoa;
            nccgh.MinQuant = nCC_GiangHang.MinQuant;

            DataAccessHelper.Save();
        }

        public static Product get(long prodID)
        {
            return DataAccessHelper.DB.Products.Where(p => p.ID == prodID).Select(p => p).First();
        }
    }
}
